﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using NHibernate;
using Thaire.Investment.BL.DTO;
using Thaire.Investment.BL.Filter;
using Thaire.Investment.BL.ScreenData;
using Thaire.Investment.Common;
using Thaire.Investment.Domains.Master;
using Thaire.Investment.Domains.Transaction;
using Thaire.Investment.Repository;

namespace Thaire.Investment.BL
{
    public class InstructionBL
    {
        ISession _session;
        public InstructionBL(ISession session)
        {
            _session = session;
        }


   

        public InstructionFormScreenData GetInstructionFormScreenData(int headerID)
        {
            InstructionHeaderRepo headerRepo = new InstructionHeaderRepo(_session);
            InstructionFormScreenData data = new InstructionFormScreenData();
            data.CompanyList = new CompanyRepo(_session).GetAll().ToList();
            data.FinancialInstrumentList = new ReferenceCodeRepo(_session).GetByDomain(ReferenceCodeRepo.DOMAIN_FINANCIAL_INSTRUMENTS, false);
            data.AssetClassList = new AssetClassRepo(_session).GetAll().ToList();
            if (headerID != 0)
            {
                data.Header = headerRepo.GetByID(headerID);
                if (data.Header.FinancialInstrumentID.GetValueOrDefault() != 0)
                {
                    ReferenceCode refCode = data.FinancialInstrumentList.Where(x => x.ID == data.Header.FinancialInstrumentID.GetValueOrDefault()).SingleOrDefault();


                    InstrumentType instrumentype = CommonUtil.GetInstrumentType(refCode.Key);
                    #region FixIncome
                    if (instrumentype == InstrumentType.FixedIncome)
                    {
                        FixIncomeSecuritiesPurchaseRepo purchaseRepo = new FixIncomeSecuritiesPurchaseRepo(_session);
                        FixIncomeSecuritiesHeaderRepo fixHeaderRepo = new FixIncomeSecuritiesHeaderRepo(_session);
                        FixIncomeSecuritiesTransactionRepo tranRepo = new FixIncomeSecuritiesTransactionRepo(_session);
                        List<InstructionDetail> instructionDetailList = headerRepo.GetInstructionDetailByHeaderID(headerID);
                        data.InstructionDataDTO = new List<InstructionDataDTO>();
                        foreach (var item in instructionDetailList)
                        {
                            InstructionDataDTO dto = new InstructionDataDTO();


                            if (dto.PurchaseID != 0)
                            {

                                FixincomesecuritiesPurchase pur = purchaseRepo.GetByID(dto.PurchaseID);
                                dto.RemainUnit = pur.RemainUnit.GetValueOrDefault();
                            }
                            if (dto.FixincomeHeaderID != 0)
                            {
                                FixIncomeSecuritiesHeader fheader = fixHeaderRepo.GetByID(dto.FixincomeHeaderID);
                                dto.IsinCode = fheader.IsinCode;
                                dto.Symbol = fheader.Symbol;

                            }


                            dto.CostYieldYTM = item.InstructionNOYield;
                            dto.CounterParty = item.InstructionCounterpartyName;
                            dto.Coupon = item.InstructionNOCoupon;
                            dto.CouponPaymentDate = item.InstructionNOCouponPaymentDate;
                            dto.FixincomeHeaderID = item.InstrumentHeaderID.GetValueOrDefault();
                            dto.InstructionDetailID = item.InstructionDetailID;
                            dto.Par = item.InstructionNOPar;
                            dto.PurchaseID = item.InstrumentPurchaseID.GetValueOrDefault();
                            dto.PurchaseValueLocalTHB = item.InstructionNOPurchaseValue;
                            dto.SettlementDate = item.InstructionNOSettlementDate;
                            //        dto.TimeToMaturityDate = item.

                            dto.TradeDate = item.InstructionNOTradeDate;
                            dto.TransactionType = item.InstructionNOTransactionType;
                            dto.TransactionID = item.InstrumentTransactionID;
                            dto.TransactionUnit = item.InstructionNOUnit.GetValueOrDefault();

                            dto.MaturityDate = item.MaturityDate;
                            dto.IssuerTerm = item.InstructionNOIssuerTerm;
                            dto.CounterPartyID = item.InstructionCounterpartyID.GetValueOrDefault();

                            dto.RefNo = item.InstructionRefNO;
                            data.InstructionDataDTO.Add(dto);
                        }
                    }
                    #endregion

                    #region UnitTrust
                    if (instrumentype == InstrumentType.UnitTrust)
                    {
                        UnitTrustHeaderRepo uthRepo = new UnitTrustHeaderRepo(_session);
                        UnitTrustTransactionRepo tranRepo = new UnitTrustTransactionRepo(_session);
                        UnitTrustInstructionDetailRepo detailRepo = new UnitTrustInstructionDetailRepo(_session);
                        List<UnitTrustInstructionDetail> detailData = detailRepo.GetListByHeaderId(headerID);
                       
                        data.InstructionUnitTrustGridDetail = new List<InstructionUnitTrustGridDetail>();
                        foreach (var item in detailData)
                        {
                            UnitTrustHeader header = uthRepo.GetByID(item.InstrumentHeaderID.GetValueOrDefault());
                            UnitTrustTransaction tran = tranRepo.GetByID(item.InstrumentTransactionID.GetValueOrDefault());
                            InstructionUnitTrustGridDetail detailDto = new InstructionUnitTrustGridDetail();

                            detailDto.UnitTrustInstructionHeaderID = item.HeaderID.GetValueOrDefault();
                            detailDto.UnitTrustInstructionDetailID = item.DetailID;
                            detailDto.UnitTrustHeaderID = item.InstrumentHeaderID.GetValueOrDefault();
                            detailDto.UnitTrustTransactionID = item.InstrumentTransactionID.GetValueOrDefault();
                            detailDto.UnitTrustTransactionType = item.TransactionType;
                            detailDto.UnitTrustFundName = item.FundName;
                            detailDto.UnitTrustCounterPartyName = item.CounterpartyName;
                            detailDto.UnitTrustUnit = item.Unit;
                            detailDto.UnitTrustPriceUnit = item.PriceUnit;
                            detailDto.UnitTrustGrossAmt = item.GrossAmount;
                            detailDto.UnitTrustComAmt = item.ComAmount;
                            detailDto.UnitTrustVatAmt = item.VatAmount;
                            detailDto.UnitTrustPurchaseValueNet = item.PurchaseValueNet;
                            detailDto.UnitTrustWhTaxAmt = item.WHTaxAmount;
                            detailDto.UnitTrustNetSettle = item.NetSettle;
                            detailDto.UnitTrustPaymentType = item.TypeofpaymentName;
                            detailDto.UnitTrustRefNo = item.RefNO;
                            detailDto.UnitTrustListStatus = item.ListedStatus;



                            detailDto.UnitTrustTimeToMaturity = (decimal)Calculator.CalTime2Mature(header.MaturityDate.GetValueOrDefault(), SessionManager.SystemDate);
                            detailDto.UnitTrustTradeDate = tran.TradeDate;
                            detailDto.UnitTrustSettlementDate = tran.SettlementDate;

                            detailDto.UnitTrustRemainUnit = header.RemainUnit;

                            UnitTrustNav nav = new UnitTrustNavRepo(_session).GetLastNavByHeaderID(header.ID);
                            UnitTrustBid bid = new UnitTrustBidRepo(_session).GetLastBidByHeaderID(header.ID);
                            if (nav != null)
                            {
                                detailDto.UnitTrustNav = nav.Nav;
                                detailDto.UnitTrustNavDate = nav.Date;
                            }
                            if (bid != null)
                            {
                                detailDto.UnitTrustBidPrice = bid.Bid;
                                detailDto.UnitTrustBidPriceDate = bid.Date;
                                detailDto.UnitTrustBidPriceAmt = bid.Bid.GetValueOrDefault() * detailDto.UnitTrustUnit.GetValueOrDefault();
                            }




                            data.InstructionUnitTrustGridDetail.Add(detailDto);
                        }



                    }
                    #endregion

                    #region Equity
                    if (instrumentype == InstrumentType.Equity)
                    {
                        EquityHeaderRepo uthRepo = new EquityHeaderRepo(_session);
                        EquityTransactionRepo tranRepo = new EquityTransactionRepo(_session);
                        EquityInstructionDetailRepo detailRepo = new EquityInstructionDetailRepo(_session);
                        List<EquityInstructionDetail> detailData = detailRepo.GetListByHeaderId(headerID);

                        data.InstructionEquityGridDetail = new List<InstructionEquityGridDetail>();
                        foreach (var item in detailData)
                        {
                            EquityHeader header = uthRepo.GetByID(item.InstrumentHeaderID.GetValueOrDefault());
                            EquityTransaction tran = tranRepo.GetByID(item.InstrumentTransactionID.GetValueOrDefault());
                            InstructionEquityGridDetail detailDto = new InstructionEquityGridDetail();

                            detailDto.EquityInstructionHeaderID = item.HeaderID.GetValueOrDefault();
                            detailDto.EquityInstructionDetailID = item.DetailID;
                            detailDto.EquityHeaderID = item.InstrumentHeaderID.GetValueOrDefault();
                            detailDto.EquityTransactionID = item.InstrumentTransactionID.GetValueOrDefault();
                            detailDto.EquityTransactionType = item.TransactionType;
                            detailDto.EquitySecName = item.SecName;
                            detailDto.EquityCounterPartyName = item.CounterpartyName;
                            detailDto.EquityUnit = item.Unit;
                            detailDto.EquityPriceUnit = item.PriceUnit;
                            detailDto.EquityGrossAmt = item.GrossAmount;
                            detailDto.EquityComAmt = item.ComAmount;
                            detailDto.EquityVatAmt = item.VatAmount;
                            detailDto.EquityPurchaseValueNet = item.PurchaseValueNet;
                            detailDto.EquityWhTaxAmt = item.WHTaxAmount;
                            detailDto.EquityNetSettle = item.NetSettle;
                            detailDto.EquityPaymentType = item.TypeofpaymentName;
                            detailDto.EquityRefNo = item.RefNO;
                            detailDto.EquityListStatus = header.ListedStatus;



                            detailDto.EquityTimeToMaturity = 0;// (decimal)Calculator.CalTime2Mature(header.MaturityDate.GetValueOrDefault(), SessionManager.SystemDate);
                            detailDto.EquityTradeDate = tran.TradeDate;
                            detailDto.EquitySettlementDate = tran.SettlementDate;

                            detailDto.EquityRemainUnit = header.RemainUnit;

               


                            data.InstructionEquityGridDetail.Add(detailDto);
                        }



                    }
                    #endregion

                    #region Deposit
                    if (instrumentype == InstrumentType.DepositAtFinancial)
                    {
                        DepositHeaderRepo uthRepo = new DepositHeaderRepo(_session);
                        DepositTransactionRepo tranRepo = new DepositTransactionRepo(_session);
                        DepositInstructionDetailRepo detailRepo = new DepositInstructionDetailRepo(_session);
                        List<DepositInstructionDetail> detailData = detailRepo.GetListByHeaderId(headerID);

                        data.InstructionDepositAtFinancialGridDetail = new List<InstructionDepositAtFinancialGridDetail>();
                        foreach (var item in detailData)
                        {
                            DepositHeader header = uthRepo.GetByID(item.InstrumentHeaderID.GetValueOrDefault());
                            DepositTransaction tran = tranRepo.GetByID(item.InstrumentTransactionID.GetValueOrDefault());
                            InstructionDepositAtFinancialGridDetail detailDto = new InstructionDepositAtFinancialGridDetail();

                            detailDto.HeaderID = item.InstructionHeaderID.GetValueOrDefault();
                            detailDto.DetailID = item.DepositInstructionDetailID;
                            detailDto.InstrumentHeaderID = item.InstrumentHeaderID.GetValueOrDefault();
                            detailDto.InstrumentTransactionID = item.InstrumentTransactionID.GetValueOrDefault();
                            detailDto.TransactionType = item.InstructionTransactionType;
                            detailDto.Amount = item.InstructionAmount;
                            detailDto.BankBranchName = item.InstructionBranchName;
                            detailDto.BankName = item.InstructionBankName;
                            detailDto.InterestRate = item.InstructionInterestRate;
                            detailDto.MaturityDate = header.MaturityDate;
                            detailDto.RefNo = item.InstructionRefNO;
                            detailDto.SettlementDate = item.InstructionSettlementDate;
                            detailDto.TradeDate = item.InstructionTradeDate;
                            detailDto.Ttm = (decimal)Calculator.CalTime2Mature(header.MaturityDate.GetValueOrDefault(), SessionManager.SystemDate);
                            detailDto.TypeOfPayment = item.InstructionTypeofpayment;
                            detailDto.VoucherNo = item.InstructionAccountVoucerNO;
                            detailDto.Term = item.InstructionTerm;







                            data.InstructionDepositAtFinancialGridDetail.Add(detailDto);
                        }



                    }
                    #endregion
                }




            }


            return data;
        }

        public ManageInstructionScreenData GetManageInstructionScreenData()
        {
            ManageInstructionScreenData data = new ManageInstructionScreenData();
            data.Company = new CompanyRepo(_session).GetAll().ToList();
            data.AssetClass = new AssetClassRepo(_session).GetAll().ToList();
            data.FinancialInstrument = new ReferenceCodeRepo(_session).GetByDomain(ReferenceCodeRepo.DOMAIN_FINANCIAL_INSTRUMENTS,true).ToList();
            data.TransactionType = new ReferenceCodeRepo(_session).GetByDomain(ReferenceCodeRepo.TRANSACTION_TYPE ,true).ToList();
            data.InstructionStatus = new ReferenceCodeRepo(_session).GetByDomain(ReferenceCodeRepo.INSTRUCTION_STATUS, true).ToList();
            return data;
        }

        public List<InstructionDataDTO> InstructionGetSelectionFixIncomeData(InstructionFormScreenData formData)
        {
            List<InstructionDataDTO> result = new List<InstructionDataDTO>();
            result.AddRange(GetSelectionFixIncome(formData));
            return result;
        }

       
        public int SaveInstruction(InstructionHeader header,List<InstructionDetail> detail)
        {
            InstructionHeaderRepo headerRepo = new InstructionHeaderRepo(_session);
            InstructionDetailRepo detailRepo = new InstructionDetailRepo(_session);
            if (header.HeaderID == 0)
            {
                header.CreateDate = SystemUtil.SysDate;
                headerRepo.Save(header);

            }
            else
            {
                headerRepo.Update(header);
            }

            foreach (var item in detail)
            {
                item.InstructionHeaderID = header.HeaderID;
              
                if (item.InstructionDetailID == 0)
                {
                      
                    detailRepo.Save(item);
                }
                else
                {
                    detailRepo.Update(item);
                }
            }
            return header.HeaderID;
        }



        public List<InstructionUnitTrustGridDetail> InstructionGetSelectionUnitTrustData(InstructionFormScreenData formData)
        {
              String extraWhere = string.Empty;
               if (!String.IsNullOrEmpty(formData.Company))
            {
                extraWhere += @"and H.UNIT_TRUST_COMPANY_ID = :CompanyID ";
            }

       
            if (formData.TradeDate.HasValue)
            {
                extraWhere += @"and P.TRADE_DATE = :INSTRUCTION_TRADE_DATE ";
            }

            if (formData.SettlementDate.HasValue)
            {
                extraWhere += @"and P.SETTLEMENT_DATE = :INSTRUCTION_SETTLEMENT_DATE ";
            }
            String sql = @"select 
                        H.UNIT_TRUST_HEADER_ID as UnitTrustHeaderID,
                       
                        P.UNIT_TRUST_TRANSACTION_ID as UnitTrustTransactionID,
                        P.TRANSACTION_TYPE as UnitTrustTransactionType , 
                        H.SYMBOL as UnitTrustFundName,
                        P.COUNTERPARTY_NAME as UnitTrustCounterPartyName , 
						 P.UNIT  as UnitTrustUnit,
					P.PRICE_UNIT  as UnitTrustPriceUnit,
					P.GROSS_AMOUNT  as UnitTrustGrossAmt,
					P.COM_AMOUNT  as UnitTrustComAmt,
					P.VAT_AMOUNT  as UnitTrustVatAmt,
					P.PURCHASE_VALUE_NET  as UnitTrustPurchaseValueNet,
					P.WH_TAX_AMOUNT  as UnitTrustWhTaxAmt,
					P.NET_SETTLE  as UnitTrustNetSettle,
					P.TYPEOFPAYMENT_NAME  as UnitTrustPaymentType,
					P.REF_NO  as UnitTrustRefNo,
					H.LISTED_STATUS  as UnitTrustListStatus,
					case when H.MATURITY_DATE <= getdate() then 0 
                        else 
                          convert(decimal(12,2), DATEDIFF(m, H.MATURITY_DATE , getdate())/12.00) 
                        end as UnitTrustTimeToMaturity, 
                       P.TRADE_DATE as UnitTrustTradeDate,
                       p.SETTLEMENT_DATE as UnitTrustSettlementDate,
                        H.REMAIN_UNIT as UnitTrustRemainUnit,
                        H.PAR as UnitTrustPar,
                        case P.TRANSACTION_TYPE 
                        when 'Purchase' then 1 
                        when 'Subscription' then 2 
                        when 'Guarantee' then 3
                        else 4  end as OrderTrans 
                      
                        from 
                        UNIT_TRUST_HEADER H, 
                        UNIT_TRUST_TRANSACTION P
                        where 
                        P.UNIT_TRUST_TRANSACTION_HEADER_ID =  H.UNIT_TRUST_HEADER_ID
                        and P.UNIT_TRUST_TRANSACTION_ID not in (select INSTRUMENT_TRANSACTION_ID from dbo.UNIT_TRUST_INSTRUCTION_DETAIL) " + extraWhere +
                      @"  Order by OrderTrans";

            ISQLQuery sqlQuery = _session.CreateSQLQuery(sql);
            if (!String.IsNullOrEmpty(formData.Company))
            {
                sqlQuery.SetParameter("CompanyID", formData.Company);
            }

            if (formData.TradeDate.HasValue)
            {
                sqlQuery.SetParameter("INSTRUCTION_TRADE_DATE", formData.TradeDate.Value);
            }

            if (formData.SettlementDate.HasValue)
            {
                sqlQuery.SetParameter("INSTRUCTION_SETTLEMENT_DATE", formData.SettlementDate.Value);
            }
            List<InstructionUnitTrustGridDetail> result = sqlQuery.SetResultTransformer(NHibernate.Transform.Transformers.AliasToBean(typeof(InstructionUnitTrustGridDetail))).List<InstructionUnitTrustGridDetail>().ToList();
          UnitTrustNavRepo navRepo = new UnitTrustNavRepo(_session);
          UnitTrustBidRepo bidRepo = new UnitTrustBidRepo(_session);
          foreach (var item in result)
          {
              UnitTrustNav nav = navRepo.GetLastNavByHeaderID(item.UnitTrustHeaderID);
              UnitTrustBid bid = bidRepo.GetLastBidByHeaderID(item.UnitTrustHeaderID);
              if (nav != null)
              {
                  item.UnitTrustNav = nav.Nav;
                  item.UnitTrustNavDate = nav.Date;
              }
              if (bid != null)
              {
                  item.UnitTrustBidPrice = bid.Bid;
                  item.UnitTrustBidPriceDate = bid.Date;
                  item.UnitTrustBidPriceAmt = bid.Bid.GetValueOrDefault() * item.UnitTrustUnit.GetValueOrDefault();
              }
          }
            return result;

        }

        public List<InstructionDepositAtFinancialGridDetail> InstructionGetSelectionDepositData(InstructionFormScreenData formData)
        {
            String extraWhere = string.Empty;
            if (!String.IsNullOrEmpty(formData.Company))
            {
                extraWhere += @"and H.DEPOSIT_COMPANY_ID = :CompanyID ";
            }


            if (formData.TradeDate.HasValue)
            {
                extraWhere += @"and P.TRADE_DATE = :INSTRUCTION_TRADE_DATE ";
            }

            if (formData.SettlementDate.HasValue)
            {
                extraWhere += @"and P.SETTLEMENT_DATE = :INSTRUCTION_SETTLEMENT_DATE ";
            }
            String sql = @"select 
                        H.DEPOSIT_HEADER_ID as InstrumentHeaderID,
                       
                        P.DEPOSIT_TRANSACTION_ID as InstrumentTransactionID,
                        P.TRANSACTION_TYPE as TransactionType , 
                        H.ACCOUNT_VOUCHER_NO as VoucherNo,
                        P.AMOUNT as Amount , 
						 H.TERM as Term,
					H.BANK_NAME  as BankName,
					H.BRANCH_NAME  as BankBranchName,
					P.SETTLEMENT_DATE  as SettlementDate,
					P.TRADE_DATE  as TradeDate,
					H.MATURITY_DATE  as MaturityDate,
					P.REF_NO  as RefNo,
					P.TYPEOFPAYMENT_ID  as TypeOfPaymentID,
					
					
			
					case when H.MATURITY_DATE <= getdate() then 0.00 
                        else 
                          convert(decimal(12,2), DATEDIFF(m, getdate() , H.MATURITY_DATE )/12.00) 
                        end as Ttm, 

                        case P.TRANSACTION_TYPE 
                        when 'DEPOSIT' then 1 
                        when 'Withdraw' then 2 
                        when 'GUARANTEE' then 3
                        else 4  end as OrderTrans 
                      
                        from 
                        DEPOSIT_HEADER H, 
                        DEPOSIT_TRANSACTION P
                        where 
                        P.DEPOSIT_HEADER_ID =  H.DEPOSIT_HEADER_ID
                        and P.DEPOSIT_TRANSACTION_ID not in (select INSTRUMENT_TRANSACTION_ID from DEPOSIT_INSTRUCTION_DETAIL) " + extraWhere +
                      @"  Order by OrderTrans";

            ISQLQuery sqlQuery = _session.CreateSQLQuery(sql);
            if (!String.IsNullOrEmpty(formData.Company))
            {
                sqlQuery.SetParameter("CompanyID", formData.Company);
            }

            if (formData.TradeDate.HasValue)
            {
                sqlQuery.SetParameter("INSTRUCTION_TRADE_DATE", formData.TradeDate.Value);
            }

            if (formData.SettlementDate.HasValue)
            {
                sqlQuery.SetParameter("INSTRUCTION_SETTLEMENT_DATE", formData.SettlementDate.Value);
            }
            List<InstructionDepositAtFinancialGridDetail> result = sqlQuery.SetResultTransformer(NHibernate.Transform.Transformers.AliasToBean(typeof(InstructionDepositAtFinancialGridDetail))).List<InstructionDepositAtFinancialGridDetail>().ToList();
            ReferenceCodeRepo refRepo = new ReferenceCodeRepo(_session);

            DepositInterestRepo insRepo = new DepositInterestRepo(_session);
            DepositInterest ins = insRepo.GetMaxApplyDate(SessionManager.SystemDate);
            foreach (var item in result)
            {
                if (item.TypeOfPaymentID.HasValue)
                {
                    ReferenceCode refcode = refRepo.GetByID(item.TypeOfPaymentID.GetValueOrDefault());
                    if (refcode != null)
                        item.TypeOfPayment = refcode.ValueEN;


                    if (ins != null)
                    {
                        item.InterestRate = ins.InterestRate;
                    }
                }


            }
            return result;

        }

        public List<InstructionEquityGridDetail> InstructionGetSelectionEquity(InstructionFormScreenData formData)
        {
            String extraWhere = string.Empty;
            if (!String.IsNullOrEmpty(formData.Company))
            {
                extraWhere += @"and H.EQUITY_COMPANY_ID = :CompanyID ";
            }


            if (formData.TradeDate.HasValue)
            {
                extraWhere += @"and P.TRADE_DATE = :INSTRUCTION_TRADE_DATE ";
            }

            if (formData.SettlementDate.HasValue)
            {
                extraWhere += @"and P.SETTLEMENT_DATE = :INSTRUCTION_SETTLEMENT_DATE ";
            }
            String sql = @"select 
                        H.EQUITY_HEADER_ID as EquityHeaderID,
                       
                        P.EQUITY_TRANSACTION_ID as EquityTransactionID,
                        P.TRANSACTION_TYPE as EquityTransactionType , 
                        H.SYMBOL as EquitySecName,
                      'P.COUNTERPARTY_NAME' as EquityCounterPartyName , 
						 P.UNIT  as EquityUnit,
					P.PRICE_UNIT  as EquityPriceUnit,
					P.GROSS_AMOUNT  as EquityGrossAmt,
					P.COM_AMOUNT  as EquityComAmt,
					P.VAT_AMOUNT  as EquityVatAmt,
					P.PURCHASE_VALUE_NET  as EquityPurchaseValueNet,
					P.WH_TAX_AMOUNT  as EquityWhTaxAmt,
					P.NET_SETTLE  as EquityNetSettle,
					'P.TYPEOFPAYMENT_NAME'  as EquityPaymentType,
					P.REF_NO  as EquityRefNo,
					H.LISTED_STATUS  as EquityListStatus,
			     	0.0001 as EquityTimeToMaturity, 
                       P.TRADE_DATE as EquityTradeDate,
                       p.SETTLEMENT_DATE as EquitySettlementDate,
                        H.REMAIN_UNIT as EquityRemainUnit,
                       -- H.PAR as EquityPar,
                        case P.TRANSACTION_TYPE 
                        when 'Purchase' then 1 
                        when 'Subscription' then 2 
                        when 'Guarantee' then 3
                        else 4  end as OrderTrans 
                      
                        from 
                        EQUITY_HEADER H, 
                        EQUITY_TRANSACTION P
                        where 
                        P.EQUITY_HEADER_ID =  H.EQUITY_HEADER_ID  
                        and P.EQUITY_TRANSACTION_ID not in (select INSTRUMENT_TRANSACTION_ID from EQUITY_INSTRUCTION_DETAIL " + extraWhere +
                      @"  Order by OrderTrans";

            ISQLQuery sqlQuery = _session.CreateSQLQuery(sql);
            if (!String.IsNullOrEmpty(formData.Company))
            {
                sqlQuery.SetParameter("CompanyID", formData.Company);
            }

            if (formData.TradeDate.HasValue)
            {
                sqlQuery.SetParameter("INSTRUCTION_TRADE_DATE", formData.TradeDate.Value);
            }

            if (formData.SettlementDate.HasValue)
            {
                sqlQuery.SetParameter("INSTRUCTION_SETTLEMENT_DATE", formData.SettlementDate.Value);
            }
            List<InstructionEquityGridDetail> result = sqlQuery.SetResultTransformer(NHibernate.Transform.Transformers.AliasToBean(typeof(InstructionEquityGridDetail))).List<InstructionEquityGridDetail>().ToList();
            UnitTrustNavRepo navRepo = new UnitTrustNavRepo(_session);
            UnitTrustBidRepo bidRepo = new UnitTrustBidRepo(_session);
            foreach (var item in result)
            {

                //todo: spec not avaliable

                //UnitTrustNav nav = navRepo.GetLastNavByHeaderID(item.UnitTrustHeaderID);
                //UnitTrustBid bid = bidRepo.GetLastBidByHeaderID(item.UnitTrustHeaderID);
                //if (nav != null)
                //{
                //    item.UnitTrustNav = nav.Nav;
                //    item.UnitTrustNavDate = nav.Date;
                //}
                //if (bid != null)
                //{
                //    item.UnitTrustBidPrice = bid.Bid;
                //    item.UnitTrustBidPriceDate = bid.Date;
                //    item.UnitTrustBidPriceAmt = bid.Bid.GetValueOrDefault() * item.UnitTrustUnit.GetValueOrDefault();
                //}
            }
            return result;

        }
        private List<InstructionDataDTO> GetSelectionFixIncome(InstructionFormScreenData formData)
        {
          //  List<ManageInstructionDTO> result = new List<ManageInstructionDTO>();
            String extraWherePurchase = string.Empty;
            String extraWhereOther = string.Empty;
            if (!String.IsNullOrEmpty(formData.Company))
            {
                extraWherePurchase += @"and H.COMPANY_ID = :CompanyID ";
                extraWhereOther += @"and H.COMPANY_ID = :CompanyID2 ";
                //  qAction += sqlQuery => sqlQuery.SetParameter("CompanyID", filter.Company);
            }

       
            if (formData.TradeDate.HasValue)
            {
                extraWherePurchase += @"and P.TRADE_DATE = :INSTRUCTION_TRADE_DATE ";
                extraWhereOther += @"and T.TRADE_DATE = :INSTRUCTION_TRADE_DATE2 ";
            }

            if (formData.SettlementDate.HasValue)
            {
                extraWherePurchase += @"and P.SETTLEMENT_DATE = :INSTRUCTION_SETTLEMENT_DATE ";
                extraWhereOther += @"and T.SETTLEMENT_DATE = :INSTRUCTION_SETTLEMENT_DATE2 ";
            }
            String Sql = @"select 
                        H.FIXINCOMESECURITIES_HEADER_ID as FixincomeHeaderID,
                        P.FIXINCOMESECURITIES_PURCHASE_ID as  PurchaseID,
                        0 as TransactionID,
                        H.ISSUER_TERM as IssuerTerm,
                        P.REF_NO as RefNo,
                        H.MATURITY_DATE as MaturityDate,
                        H.ISIN_CODE as IsinCode,
                        H.SYMBOL as Symbol,
                        P.TRANSACTION_TYPE as TransactionType , 
                        P.COUNTERPARTY_ID as CounterPartyID,
                        P.COUNTERPARTY_NAME as CounterParty , 
                        case when H.MATURITY_DATE <= getdate() then 0 
                        else 
                          convert(decimal(12,2), DATEDIFF(m, H.MATURITY_DATE , getdate())/12.00) 
                        end as TimeToMaturityDate , 
                        P.TRADE_DATE as TradeDate, 
                        P.SETTLEMENT_DATE as  SettlementDate, 
                        P.UNIT as  TransactionUnit, 
                        P.REMAIN_UNIT as  RemainUnit, 
                        P.PAR as Par , 
                        P.YIELD as  CostYieldYTM, 
                        H.COUPON_PAYMENT_DATE as  CouponPaymentDate, 
                        H.COUPON as Coupon , 
                        P.PURCHASE_VALUE_TH as PurchaseValueLocalTHB , 
                        case P.TRANSACTION_TYPE 
                        when 'Purchase' then 1 
                        when 'Subscription' then 2 end as OrderTrans 
                        from 
                        FIXINCOMESECURITIES_HEADER H, 
                        FIXINCOMESECURITIES_PURCHASE P
                        where 
                        P.FIXINCOMESECURITIES_HEADER_ID =  H.FIXINCOMESECURITIES_HEADER_ID
 and P.FIXINCOMESECURITIES_PURCHASE_ID not in (select INSTRUMENT_PURCHASE_ID from INSTRUCTION_DETAIL) " + extraWherePurchase +
                        
                      @"  union all
                        select 
                        H.FIXINCOMESECURITIES_HEADER_ID as FixincomeHeaderID,
                        P.FIXINCOMESECURITIES_PURCHASE_ID as  PurchaseID,
                        T.TRANSACTION_ID as TransactionID,
                        H.ISSUER_TERM as IssuerTerm,
                        T.REF_NO as RefNo,
                        H.MATURITY_DATE as MaturityDate,
                        H.ISIN_CODE as IsinCode,
                        H.SYMBOL as Symbol,
                        T.TRANSACTION_TYPE as TransactionType , 
                        T.COUNTERPARTY_ID as CounterPartyID,
                        T.COUNTERPARTY_NAME  as  CounterParty , 
                        case when H.MATURITY_DATE <= getdate() then 0 
                        else 
                          convert(decimal(12,2), DATEDIFF(m, H.MATURITY_DATE , getdate())/12.00) 
                        end as  TimeToMaturityDate ,  
                        T.TRADE_DATE as  TradeDate,   
                        T.SETTLEMENT_DATE as SettlementDate, 
                        T.UNIT as TransactionUnit,
                        P.REMAIN_UNIT as RemainUnit, 
                        P.PAR as Par,
                        T.YIELD as CostYieldYTM,
                        H.COUPON_PAYMENT_DATE as CouponPaymentDate,
                        H.COUPON as Coupon,
                        P.PURCHASE_VALUE_TH as PurchaseValueLocalTHB ,
                        3 as Order_Trans 
                        from
                        FIXINCOMESECURITIES_PURCHASE P, 
                        FIXINCOMESECURITIES_HEADER H, 
                        FIXINCOMESECURITIES_TRANSACTION T
                        where
                        P.FIXINCOMESECURITIES_HEADER_ID =  H.FIXINCOMESECURITIES_HEADER_ID   
                        and 
                        P.FIXINCOMESECURITIES_PURCHASE_ID  =  T.PURCHASE_ID 
and T.TRANSACTION_ID not in (select INSTRUMENT_TRANSACTION_ID from INSTRUCTION_DETAIL) " + extraWhereOther +
                       @" order by OrderTrans";
         

           
            ISQLQuery sqlQuery = _session.CreateSQLQuery(Sql);
            if (!String.IsNullOrEmpty(formData.Company))
            {
                sqlQuery.SetParameter("CompanyID", formData.Company);
                sqlQuery.SetParameter("CompanyID2", formData.Company);
            }

            //if (!String.IsNullOrEmpty(formData.FinancialInstrument))
            //{
            //    sqlQuery.SetParameter("INSTRUCTION_FINANCIAL_INSTRUMENT_ID", formData.FinancialInstrument);
            //}
            if (formData.TradeDate.HasValue)
            {
                sqlQuery.SetParameter("INSTRUCTION_TRADE_DATE", formData.TradeDate.Value);
                sqlQuery.SetParameter("INSTRUCTION_TRADE_DATE2", formData.TradeDate.Value);
            }

            if (formData.SettlementDate.HasValue)
            {
                sqlQuery.SetParameter("INSTRUCTION_SETTLEMENT_DATE", formData.SettlementDate.Value);
                sqlQuery.SetParameter("INSTRUCTION_SETTLEMENT_DATE2", formData.SettlementDate.Value);
            }
            List<InstructionDataDTO> result = sqlQuery.SetResultTransformer(NHibernate.Transform.Transformers.AliasToBean(typeof(InstructionDataDTO))).List<InstructionDataDTO>().ToList();
            foreach (var item in result)
            {
                item.TimeToMaturityDate = (decimal)Calculator.CalTime2Mature(item.MaturityDate.GetValueOrDefault(), SessionManager.SystemDate);
            }
            return result;
        }
        private List<ManageInstructionDTO> SearchInstructionUnitTrust(SearchInstructionFilter filter)
        {
            //UnitTrustInstructionDetail detail;
            //detail.TransactionType
            List<ManageInstructionDTO> result = new List<ManageInstructionDTO>();
            Action<IQuery> qAction = query => { };
            String sql = @"    from InstructionHeader header,UnitTrustInstructionDetail detail 
            where header.HeaderID = detail.HeaderID  ";

            if (filter.CompanyID.HasValue)
            {
                sql += @"and header.CompanyID = :CompanyID ";
                qAction += query => query.SetParameter("CompanyID", filter.CompanyID.Value);
            }
            if (filter.FinancialInstrument.HasValue)
            {
                sql += @"and header.FinancialInstrumentID = :FinancialInstrumentID ";
                qAction += query => query.SetParameter("FinancialInstrumentID", filter.FinancialInstrument.Value);
            }
            if (filter.AssetClass.HasValue)
            {
                AssetClassRepo repo = new AssetClassRepo(_session);
                AssetClass asset = repo.GetByID(filter.AssetClass.GetValueOrDefault());
                if (asset != null)
                {
                    if (filter.FinancialInstrument.HasValue)
                    {
                        sql += @"or header.FinancialInstrumentID = :FinancialInstrumentID2 ";
                        qAction += query => query.SetParameter("FinancialInstrumentID2", asset.FinancialInstrumentsReferenceCodeID.GetValueOrDefault());
                    }
                    else
                    {
                        sql += @"and header.FinancialInstrumentID = :FinancialInstrumentID ";
                        qAction += query => query.SetParameter("FinancialInstrumentID", asset.FinancialInstrumentsReferenceCodeID.GetValueOrDefault());
                    }
                }


            }

            if (filter.CreateDateForm.HasValue)
            {
                sql += @"and header.CreateDate >= :CreateDateForm ";
                qAction += query => query.SetParameter("CreateDateForm", filter.CreateDateForm.Value.Date);
            }


            if (filter.CreateDateTO.HasValue)
            {
                sql += @"and header.CreateDate <= :CreateDateTO ";
                qAction += query => query.SetParameter("CreateDateTO", filter.CreateDateTO.Value.AddDays(1).AddMilliseconds(-1));
            }

            if (!String.IsNullOrEmpty(filter.RefNo))
            {
                sql += @"and detail.RefNO = :InstructionRefNO ";
                qAction += query => query.SetParameter("InstructionRefNO", filter.RefNo);

            }

            if (!String.IsNullOrEmpty(filter.SelectTransaction))
            {
                sql += @"and detail.TransactionType = :InstructionNOTransactionType ";
                qAction += query => query.SetParameter("InstructionNOTransactionType", filter.SelectTransaction);
            }

            var q = _session.CreateQuery(sql);

            qAction(q);
            var rows = q.List();
            foreach (var item in rows)
            {
                object[] data = (object[])item;
                InstructionHeader header = (InstructionHeader)data[0];
                UnitTrustInstructionDetail detail = (UnitTrustInstructionDetail)data[1];
                ManageInstructionDTO dto = new ManageInstructionDTO();

                dto.AssetClass = header.AssetClassName;
                dto.Company = header.CompanyName;
                dto.Createby = header.CreateUser;
                dto.CreateDate = header.CreateDate;
                dto.FinancialInstrumentName = header.FinancialInstrumentName;
                dto.InstructionHeaderID = header.HeaderID;
                dto.RefNo = detail.RefNO ;
                dto.Status = header.Status.ToString();
                result.Add(dto);
            }
            return result;
        }

        private List<ManageInstructionDTO> SearchInstructionDeposit(SearchInstructionFilter filter)
        {
            //UnitTrustInstructionDetail detail;
            //detail.TransactionType
            List<ManageInstructionDTO> result = new List<ManageInstructionDTO>();
            Action<IQuery> qAction = query => { };
            String sql = @"    from InstructionHeader header,DepositInstructionDetail detail 
            where header.HeaderID = detail.InstructionHeaderID  ";

            if (filter.CompanyID.HasValue)
            {
                sql += @"and header.CompanyID = :CompanyID ";
                qAction += query => query.SetParameter("CompanyID", filter.CompanyID.Value);
            }
            if (filter.FinancialInstrument.HasValue)
            {
                sql += @"and header.FinancialInstrumentID = :FinancialInstrumentID ";
                qAction += query => query.SetParameter("FinancialInstrumentID", filter.FinancialInstrument.Value);
            }
            if (filter.AssetClass.HasValue)
            {
                AssetClassRepo repo = new AssetClassRepo(_session);
                AssetClass asset = repo.GetByID(filter.AssetClass.GetValueOrDefault());
                if (asset != null)
                {
                    if (filter.FinancialInstrument.HasValue)
                    {
                        sql += @"or header.FinancialInstrumentID = :FinancialInstrumentID2 ";
                        qAction += query => query.SetParameter("FinancialInstrumentID2", asset.FinancialInstrumentsReferenceCodeID.GetValueOrDefault());
                    }
                    else
                    {
                        sql += @"and header.FinancialInstrumentID = :FinancialInstrumentID ";
                        qAction += query => query.SetParameter("FinancialInstrumentID", asset.FinancialInstrumentsReferenceCodeID.GetValueOrDefault());
                    }
                }


            }

            if (filter.CreateDateForm.HasValue)
            {
                sql += @"and header.CreateDate >= :CreateDateForm ";
                qAction += query => query.SetParameter("CreateDateForm", filter.CreateDateForm.Value.Date);
            }


            if (filter.CreateDateTO.HasValue)
            {
                sql += @"and header.CreateDate <= :CreateDateTO ";
                qAction += query => query.SetParameter("CreateDateTO", filter.CreateDateTO.Value.AddDays(1).AddMilliseconds(-1));
            }

            if (!String.IsNullOrEmpty(filter.RefNo))
            {
                sql += @"and detail.RefNO = :InstructionRefNO ";
                qAction += query => query.SetParameter("InstructionRefNO", filter.RefNo);

            }

            if (!String.IsNullOrEmpty(filter.SelectTransaction))
            {
                sql += @"and detail.TransactionType = :InstructionNOTransactionType ";
                qAction += query => query.SetParameter("InstructionNOTransactionType", filter.SelectTransaction);
            }

            var q = _session.CreateQuery(sql);

            qAction(q);
            var rows = q.List();
            foreach (var item in rows)
            {
                object[] data = (object[])item;
                InstructionHeader header = (InstructionHeader)data[0];
                DepositInstructionDetail detail = (DepositInstructionDetail)data[1];
                ManageInstructionDTO dto = new ManageInstructionDTO();

                dto.AssetClass = header.AssetClassName;
                dto.Company = header.CompanyName;
                dto.Createby = header.CreateUser;
                dto.CreateDate = header.CreateDate;
                dto.FinancialInstrumentName = header.FinancialInstrumentName;
                dto.InstructionHeaderID = header.HeaderID;
                dto.RefNo = detail.InstructionRefNO;
                dto.Status = header.Status.ToString();
                result.Add(dto);
            }
            return result;
        }

        private List<ManageInstructionDTO> SearchInstructionEquity(SearchInstructionFilter filter)
        {
          //  EquityInstructionDetail  detail;
          
            List<ManageInstructionDTO> result = new List<ManageInstructionDTO>();
            Action<IQuery> qAction = query => { };
            String sql = @"    from InstructionHeader header,EquityInstructionDetail detail 
            where header.HeaderID = detail.HeaderID  ";

            if (filter.CompanyID.HasValue)
            {
                sql += @"and header.CompanyID = :CompanyID ";
                qAction += query => query.SetParameter("CompanyID", filter.CompanyID.Value);
            }
            if (filter.FinancialInstrument.HasValue)
            {
                sql += @"and header.FinancialInstrumentID = :FinancialInstrumentID ";
                qAction += query => query.SetParameter("FinancialInstrumentID", filter.FinancialInstrument.Value);
            }
            if (filter.AssetClass.HasValue)
            {
                AssetClassRepo repo = new AssetClassRepo(_session);
                AssetClass asset = repo.GetByID(filter.AssetClass.GetValueOrDefault());
                if (asset != null)
                {
                    if (filter.FinancialInstrument.HasValue)
                    {
                        sql += @"or header.FinancialInstrumentID = :FinancialInstrumentID2 ";
                        qAction += query => query.SetParameter("FinancialInstrumentID2", asset.FinancialInstrumentsReferenceCodeID.GetValueOrDefault());
                    }
                    else
                    {
                        sql += @"and header.FinancialInstrumentID = :FinancialInstrumentID ";
                        qAction += query => query.SetParameter("FinancialInstrumentID", asset.FinancialInstrumentsReferenceCodeID.GetValueOrDefault());
                    }
                }


            }

            if (filter.CreateDateForm.HasValue)
            {
                sql += @"and header.CreateDate >= :CreateDateForm ";
                qAction += query => query.SetParameter("CreateDateForm", filter.CreateDateForm.Value.Date);
            }


            if (filter.CreateDateTO.HasValue)
            {
                sql += @"and header.CreateDate <= :CreateDateTO ";
                qAction += query => query.SetParameter("CreateDateTO", filter.CreateDateTO.Value.AddDays(1).AddMilliseconds(-1));
            }

            if (!String.IsNullOrEmpty(filter.RefNo))
            {
                sql += @"and detail.RefNO = :InstructionRefNO ";
                qAction += query => query.SetParameter("InstructionRefNO", filter.RefNo);

            }

            if (!String.IsNullOrEmpty(filter.SelectTransaction))
            {
                sql += @"and detail.TransactionType = :InstructionNOTransactionType ";
                qAction += query => query.SetParameter("InstructionNOTransactionType", filter.SelectTransaction);
            }

            var q = _session.CreateQuery(sql);

            qAction(q);
            var rows = q.List();
            foreach (var item in rows)
            {
                object[] data = (object[])item;
                InstructionHeader header = (InstructionHeader)data[0];
                EquityInstructionDetail detail = (EquityInstructionDetail)data[1];
                ManageInstructionDTO dto = new ManageInstructionDTO();

                dto.AssetClass = header.AssetClassName;
                dto.Company = header.CompanyName;
                dto.Createby = header.CreateUser;
                dto.CreateDate = header.CreateDate;
                dto.FinancialInstrumentName = header.FinancialInstrumentName;
                dto.InstructionHeaderID = header.HeaderID;
                dto.RefNo = detail.RefNO;
                dto.Status = header.Status.ToString();
                result.Add(dto);
            }
            return result;
        }

        private List<ManageInstructionDTO> SearchInstructionFixincome(SearchInstructionFilter filter)
        {
            
            List<ManageInstructionDTO> result = new List<ManageInstructionDTO>();
            Action<IQuery> qAction = query => { };
            String sql = @"    from InstructionHeader header,InstructionDetail detail 
            where header.HeaderID = detail.InstructionHeaderID  ";

            if (filter.CompanyID.HasValue)
            {
                sql += @"and header.CompanyID = :CompanyID ";
                qAction += query => query.SetParameter("CompanyID", filter.CompanyID.Value);
            }
            if (filter.FinancialInstrument.HasValue)
            {
                sql += @"and header.FinancialInstrumentID = :FinancialInstrumentID ";
                qAction += query => query.SetParameter("FinancialInstrumentID", filter.FinancialInstrument.Value);
            }
            if (filter.AssetClass.HasValue)
            {
                AssetClassRepo repo = new AssetClassRepo(_session);
                AssetClass asset = repo.GetByID(filter.AssetClass.GetValueOrDefault());
                if (asset != null)
                {
                    if (filter.FinancialInstrument.HasValue)
                    {
                        sql += @"or header.FinancialInstrumentID = :FinancialInstrumentID2 ";
                        qAction += query => query.SetParameter("FinancialInstrumentID2", asset.FinancialInstrumentsReferenceCodeID.GetValueOrDefault());
                    }
                    else
                    {
                        sql += @"and header.FinancialInstrumentID = :FinancialInstrumentID ";
                        qAction += query => query.SetParameter("FinancialInstrumentID", asset.FinancialInstrumentsReferenceCodeID.GetValueOrDefault());
                    }
                }


            }

            if (filter.CreateDateForm.HasValue)
            {
                sql += @"and header.CreateDate >= :CreateDateForm ";
                qAction += query => query.SetParameter("CreateDateForm", filter.CreateDateForm.Value.Date);
            }


            if (filter.CreateDateTO.HasValue)
            {
                sql += @"and header.CreateDate <= :CreateDateTO ";
                qAction += query => query.SetParameter("CreateDateTO", filter.CreateDateTO.Value.AddDays(1).AddMilliseconds(-1));
            }

            if (!String.IsNullOrEmpty(filter.RefNo))
            {
                sql += @"and detail.InstructionRefNO = :InstructionRefNO ";
                qAction += query => query.SetParameter("InstructionRefNO", filter.RefNo);

            }

            if (!String.IsNullOrEmpty(filter.SelectTransaction))
            {
                sql += @"and detail.InstructionNOTransactionType = :InstructionNOTransactionType ";
                qAction += query => query.SetParameter("InstructionNOTransactionType", filter.SelectTransaction);
            }

            var q = _session.CreateQuery(sql);

            qAction(q);
            var rows = q.List();
            foreach (var item in rows)
            {
                object[] data = (object[])item;
                InstructionHeader header = (InstructionHeader)data[0];
                InstructionDetail detail = (InstructionDetail)data[1];
                ManageInstructionDTO dto = new ManageInstructionDTO();

                dto.AssetClass = header.AssetClassName;
                dto.Company = header.CompanyName;
                dto.Createby = header.CreateUser;
                dto.CreateDate = header.CreateDate;
                dto.FinancialInstrumentName = header.FinancialInstrumentName;
                dto.InstructionHeaderID = header.HeaderID;
                dto.RefNo = detail.InstructionRefNO;
                dto.Status = header.Status.ToString();
                result.Add(dto);
            }
            return result;
        }
        public List<ManageInstructionDTO> SearchInstructionDTO(SearchInstructionFilter filter)
        {
            List<ManageInstructionDTO> result = new List<ManageInstructionDTO>();
            if (filter.FinancialInstrument == null)//searchALL instrument
            {
                result.AddRange(SearchInstructionFixincome(filter));
                result.AddRange(SearchInstructionUnitTrust(filter));
                result.AddRange(SearchInstructionEquity(filter));
                result.AddRange(SearchInstructionDeposit(filter));

            }
            else
            {
                ReferenceCode refCode = new ReferenceCodeRepo(_session).GetByID(filter.FinancialInstrument.GetValueOrDefault());
                if (refCode != null)
                {
                    InstrumentType instrumentType = CommonUtil.GetInstrumentType(refCode.Key);
                    switch (instrumentType)
                    {

                        case InstrumentType.FixedIncome:
                            result.AddRange(SearchInstructionFixincome(filter));
                            break;
                        case InstrumentType.Equity:
                            result.AddRange(SearchInstructionEquity(filter));
                            break;
                        case InstrumentType.UnitTrust:
                            result.AddRange(SearchInstructionUnitTrust(filter));
                            break;
                        case InstrumentType.Derivatives:
                            break;
                        case InstrumentType.Loans:
                            break;
                        case InstrumentType.DepositAtFinancial:
                            result.AddRange(SearchInstructionDeposit(filter));
                            break;
                        default:
                            break;
                    }
                }
            }


            return result.OrderByDescending(x => x.CreateDateForSort).ToList();

        }


        public int SaveInstructionDeposit(InstructionHeader header, List<DepositInstructionDetail> detail)
        {

            InstructionHeaderRepo headerRepo = new InstructionHeaderRepo(_session);
            DepositInstructionDetailRepo detailRepo = new DepositInstructionDetailRepo(_session);
            if (header.HeaderID == 0)
            {
                header.CreateDate = SystemUtil.SysDate;
                headerRepo.Save(header);

            }
            else
            {
                headerRepo.Update(header);
            }

            foreach (var item in detail)
            {
                item.InstructionHeaderID = header.HeaderID;

                if (item.DepositInstructionDetailID == 0)
                {
                    item.CreateDate = SessionManager.SystemDate;
                    detailRepo.Save(item);
                }
                else
                {
                    item.ModifyDate = SessionManager.SystemDate;
                    detailRepo.Update(item);
                }
            }
            return header.HeaderID;

        }

        public int SaveInstructionUnitTrust(InstructionHeader header, List<UnitTrustInstructionDetail> detail)
        {
         
            InstructionHeaderRepo headerRepo = new InstructionHeaderRepo(_session);
            UnitTrustInstructionDetailRepo detailRepo = new UnitTrustInstructionDetailRepo(_session);
            if (header.HeaderID == 0)
            {
                header.CreateDate = SystemUtil.SysDate;
                headerRepo.Save(header);

            }
            else
            {
                headerRepo.Update(header);
            }

            foreach (var item in detail)
            {
                item.HeaderID = header.HeaderID;
              
                if (item.DetailID == 0)
                {
                    item.CreateDate = SessionManager.SystemDate;
                    detailRepo.Save(item);
                }
                else
                {
                   // item.M = SessionManager.SystemDate;
                    detailRepo.Update(item);
                }
            }
           return header.HeaderID;
        
        }

        public int SaveInstructionEquity(InstructionHeader header, List<EquityInstructionDetail> detail)
        {
            InstructionHeaderRepo headerRepo = new InstructionHeaderRepo(_session);
            EquityInstructionDetailRepo detailRepo = new EquityInstructionDetailRepo(_session);
            if (header.HeaderID == 0)
            {
                header.CreateDate = SystemUtil.SysDate;
                headerRepo.Save(header);

            }
            else
            {
                headerRepo.Update(header);
            }

            foreach (var item in detail)
            {
                item.HeaderID = header.HeaderID;

                if (item.DetailID == 0)
                {
                    item.CreateDate = SessionManager.SystemDate;
                    detailRepo.Save(item);
                }
                else
                {
                    // item.M = SessionManager.SystemDate;
                    detailRepo.Update(item);
                }
            }
            return header.HeaderID;
        }
    }
}
